package com.dongwu.manager.dao;

import java.util.List;

import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import com.dongwu.common.dao.BaseDao;
import com.dongwu.manager.entity.DeviceProductRecord;
import org.springframework.transaction.annotation.Transactional;

@Repository
public interface DeviceProductRecordDao  extends BaseDao<DeviceProductRecord, String> {

//	public List<DeviceProductRecord> findByDeviceid(String deviceId);
	
	public List<DeviceProductRecord> findByDeviceid(String deviceId);
	
	public List<DeviceProductRecord> findByProductid(String productId);
	
	@Query(value="select COUNT("
			+ "CASE WHEN dpr.end_time is NULL  and dpr.start_time is NULL THEN '1' "
			+ "WHEN dpr.end_time is NOT NULL  and dpr.start_time is NULL and dpr.end_time >= now()  THEN '2' "
			+ "WHEN dpr.end_time is NULL  and dpr.start_time is NOT NULL and dpr.start_time <= now() THEN '3' "
			+ "WHEN dpr.end_time is NOT NULL  and dpr.start_time is NOT NULL and dpr.start_time <= now() and dpr.end_time >= now() THEN '4' "
			+ "ELSE NULL "
			+ "END) "
			+ "AS dprcount "
			+ "FROM device_product_record dpr "
			+ "where userid = ?1", nativeQuery=true)//原生sql语句
	public Long countByUserid(String userid);

	@Modifying
	@Transactional
	@Query(value="select * from device_product_record where productid in(?1) ", nativeQuery=true)//原生sql语句
	public List<DeviceProductRecord> getDeviceProductRecord(List<String> productid);
}
